﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using BiocityFilmquizAdmin.Src.Domain;

namespace BiocityFilmquizAdmin.Src.DatabaseAccess
{
    public class StatsHelper
    {
        private DbAcces dbAcces;
        private static StatsHelper instance;

        private StatsHelper()
        {
            dbAcces = DbAcces.getInstance();
        }

        public static StatsHelper getInstance()
        {
            if(instance == null)
            {
                instance = new StatsHelper();
            }

            return instance;
        }

        public List<String> getPlayersThatAnsweredQuestion(int question)
        {
            List<String> userNames = new List<string>();
            String query = "select userName from users join answers where users.userId = answers.user and answers.question = " + question + " and answers.round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);

            while (reader.Read())
            {
                userNames.Add(reader.GetString(0));
            }
            reader.Close();
            dbAcces.CloseConnection();

            return userNames;
        }

        /// <summary>
        /// method that returns wrong answers for a question, String is answer, int i how many times that answer has been tried for all users
        /// </summary>
        /// <param name="question"></param>
        /// <returns></returns>
        public List<WrongAnswer> getWrongAnswers(int question)
        {
            //TODO:
            List<WrongAnswer> wrongAnswers = new List<WrongAnswer>();
            List<String> index = new List<String>();
            
            String query = "Select answer from wrongAnswers where question = " + question + " and round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);

            while (reader.Read())
            {
                String wrongAnswer = reader.GetString(0);

                if (index.Contains(wrongAnswer.ToUpper()))
                {
                    int i = index.IndexOf(wrongAnswer.ToUpper());
                    wrongAnswers[i].count += 1;
                }
                else
                {
                    index.Add(wrongAnswer.ToUpper());
                    wrongAnswers.Add(new WrongAnswer(wrongAnswer));
                }
            }

            wrongAnswers.Sort();

            dbAcces.CloseConnection();
            return wrongAnswers;
        }

        /// <summary>
        /// Method that returns comments for a question. First String is comment, second is username of user that palced comment
        /// </summary>
        /// <param name="question"></param>
        /// <returns></returns>
        public List<Comment> getCommentsForQuestion(int question)
        {
            List<Comment> comments = new List<Comment>();

            String query = "select comments.comment, comments.time, users.userName from comments join users where users.userID = comments.userId and comments.question = " + question + " and comments.round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);

            while (reader.Read())
            {
                comments.Add(new Comment(reader.GetString(0), reader.GetString(2), question, reader.GetDateTime(1).ToShortDateString()));
            }

            dbAcces.CloseConnection();
            return comments;
        }

        public void insertComment(int question, String comment, int userId)
        {
            String query = "INSERT INTO comments (`userId`, `comment`, `question`, `round`) VALUES (" + userId + ", ' " + comment + "', " + question + ", " + Statics.getInstance().getCurrentRound() + ");";
            dbAcces.Insert(query);
        }

        public int getNumberOfCorrectAnswers()
        {
            String query = "select count(*) from answers where round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);

            reader.Read();

            int answers = reader.GetInt32(0);
            dbAcces.CloseConnection();

            return answers;
        }

        public int getNumberOfWrongAnswers()
        {
            String query = "select count(*) from wrongAnswers where round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);
            reader.Read();

            int wrongAnswers = reader.GetInt32(0);
            dbAcces.CloseConnection();
            return wrongAnswers;
        }

        public List<String> getPlayersThatCompleted()
        {
            List<String> playersCompleted = new List<string>();
            String query = "select userName from users join playersCompleted where users.userID = playersCompleted.user and playersCompleted.round = " + Statics.getInstance().getCurrentRound() + " order by playersCompleted.time;";
            MySqlDataReader reader = dbAcces.Select(query);

            while (reader.Read())
            {
                playersCompleted.Add(reader.GetString(0));
            }
            dbAcces.CloseConnection();
            return playersCompleted;
        }

        /// <summary>
        /// Method that returns a List containing number of players that answered X questions. The index of the List references the number of questions answered - 1
        /// </summary>
        /// <returns></returns>
        public List<int> getNumberOfPlayersThatAnsweredXQuestions()
        {
            List<int> questionsAnswered = new List<int>();
            List<int> activeUsers = getActiveUsers();

            for (int i = 0; i < Statics.getInstance().getNumberOfQuestions(); i++)
            {
                questionsAnswered.Add(0);
            }

            foreach (int user in activeUsers)
            {
                int count = getNumberOfAnswersForUser(user);
                questionsAnswered[count - 1] += 1;
            }
            
            return questionsAnswered;
        }

        public int getNumberOfPlayersThatAnsweredQuestion(int question)
        {
            String query = "select count(*) from answers where question = " + question + " and round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);
            reader.Read();

            int players = reader.GetInt32(0);
            dbAcces.CloseConnection();

            return players;
        }

        public int getNumberOfAnswersForUser(int userID)
        {
            String query = "select count(*) from answers where user = " + userID + " and round = " + Statics.getInstance().getCurrentRound() + ";";
            MySqlDataReader reader = dbAcces.Select(query);
            reader.Read();

            int answers = reader.GetInt32(0);
            dbAcces.CloseConnection();

            return answers;
        }

        public List<int> getActiveUsers()
        {
            List<int> users = new List<int>();
            String query = "select distinct user from answers where round = " + Statics.getInstance().getCurrentRound() + ";";

            MySqlDataReader reader = dbAcces.Select(query);

            while (reader.Read())
            {
                users.Add(reader.GetInt32(0));
            }

            dbAcces.CloseConnection();

            return users;
        }

        public double getPercentageForQuestion(int question)
        {
            double users = getActiveUsers().Count;
            double answered = getNumberOfPlayersThatAnsweredQuestion(question);

            double answer = (answered / users) * 100;

            return answer;
        }
    }
}